package de.hsos.db.a01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class DBVerbindung {
	private Connection con;
	private ResultSet rs;
	
	private String user = "odwittkoe";
	private String password = "dennis123";
	private final static String host = "oracle-srv.edvsz.hs-osnabrueck.de";
	private final static String port = "1521";
	private final static String database = "";
	private final static String server = "ora11";
	private final static String driver = "oracle.jdbc.driver.OracleDriver";
	
	public DBVerbindung(String user, String password) {
		this.user = user;
		this.password = password;
	}
	
	public void aufbauen() throws SQLException {
		String url = null;
		
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		url = "jdbc:oracle:thin:"+"/@"+host+":"+port+":"+database+server;
		con = DriverManager.getConnection(url, user, password);

	}
	
	public void schliessen() throws SQLException {
		con.close();
		
	}
	
	public boolean aufgebaut() {
		if (con != null)
			return true;
		
		return false;
	}
	
	public Connection getConnection() {
		return con;
	}

	public void selectAnfrage(String befehl) throws SQLException {
		
		Statement stmt = con.createStatement();
	    rs = stmt.executeQuery(befehl);
	    
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int cols = rsmd.getColumnCount();

	    for(int i=1; i<=cols; i++)
	        System.out.print(rsmd.getColumnLabel(i)+"\t");
	    System.out.println("\n-------------------------------");

	    while(rs.next())
	    {
	        // eine zeile ausgeben
	        for(int i=1; i<=cols; i++)
	            System.out.print(rs.getString(i)+"\t");

	        System.out.println();
	    }
	    rs.close();
		
	}
	
	public void preparedStatementAnfrage(String name) throws SQLException {
		PreparedStatement pstmt = con.prepareStatement("update Kunde2 set name = ? where knr = 1");
		pstmt.setString(1, name);
		pstmt.executeUpdate();
	}
	
	public void transaction(int bnr, int knr1, int knr2) throws SQLException {
		try {
			con.setAutoCommit(false);
			
			PreparedStatement insertBuchung = con.prepareStatement(
				"insert into Buchung values(1, ?");
			insertBuchung.setInt(bnr, knr1);
			insertBuchung.executeUpdate();
			insertBuchung.setInt(bnr, knr2);
			insertBuchung.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			con.rollback();
			e.printStackTrace();
		} finally {
			con.setAutoCommit(true);
		}
	}
}
